# -*- coding: utf-8 -*-
# author: liangzilong
import pymysql
import os
import yaml
from pathlib import Path
# 下载yaml库 pip install pyyaml
# 下载yaml库 pip install pymysql


class DBConnector:
    def __init__(self) -> None:
        self._db = pymysql.connect(
            host="127.0.0.1",
            user="root",
            password="123456",

        )
        self._cursor = self._db.cursor()

        self._common = 'common'

        res = Path(__file__).resolve().parent
        file_path = os.path.join(res, 'db_schema.yaml')

        with open(file_path, 'r', encoding="utf-8") as f:
            self._table_schema = yaml.safe_load(f)

    def run(self):
        return self._db

    def _check_database(self, database_name: str) -> bool:
        '''
        Check the existence of the database.
        Return True if the database exists, False otherwise.
        '''
        result = self._cursor.execute(f'SHOW DATABASES LIKE "{database_name}"')
        return True if result else False

    def _get_create_table_command(self, database_name: str, table_name: str, table_struct: dict) -> str:
        '''
        Generate the SQL command to create a table.
        Return the SQL command as a string.
        '''
        sql = f'CREATE TABLE {database_name}.{table_name} ('
        column_list = []
        for column_name, column_property in table_struct.items():
            if column_name == 'comment':
                continue
            column_list.append(f'{column_name} {column_property}')
        sql += ', '.join(column_list) + ') COMMENT "' + \
            table_struct['comment'] + '"'
        return sql

    def create_common_database(self) -> bool:
        '''
        Create the common database if it doesn't exist.
        Create the tables if they don't exist.
        Return the result of the creation.
        '''
        if self._check_database(self._common):
            print(f'{self._common} database already exists!')
            return False
        self._cursor.execute(f'CREATE DATABASE {self._common}')
        for table, table_struct in self._table_schema[self._common].items():
            sql = self._get_create_table_command(
                self._common, table, table_struct)
            print(sql)
            print('\n\n')
            self._cursor.execute(sql)
        self._db.commit()
        print(f'{self._common} database created!')
        return True

    def create_project_database(self, project_name: str) -> bool:
        '''
        Create the project database if it doesn't exist.
        Create the tables if they don't exist.
        Return the result of the creation.
        '''
        if self._check_database(project_name):
            print(f'{project_name} database already exists!')
            return False
        # COLLATE = utf8_general_ci   utf8mb4_bin    #utf8mb4_general_ci
        self._cursor.execute(
            f'CREATE DATABASE {project_name} CHARACTER SET = utf8mb4')
        for table, table_struct in self._table_schema['project'].items():
            print(f'[{table}]')
            sql = self._get_create_table_command(
                project_name, table, table_struct)
            print(sql)
            print('\n\n')
            self._cursor.execute(sql)
        self._db.commit()
        print(f'{project_name} database created!')
        return True

    def remove_project_database(self, project_name: str) -> bool:
        '''
        Remove the project database if it exists.
        Return the result of the removal.
        '''
        return self._remove_database(project_name)

    def remove_common_database(self) -> bool:
        '''
        Remove the common database if it exists.
        Return the result of the removal.
        '''
        return self._remove_database(self._common)

    def _remove_database(self, database_name: str) -> bool:
        '''
        Remove the database if it exists.
        Return the result of the removal.
        '''
        if not self._check_database(database_name):
            print(f'{database_name} database does not exist!')
            return True
        try:
            self._cursor.execute(f'DROP DATABASE {database_name}')
            self._db.commit()
            print(f'{database_name} database removed!')
            return True
        except:
            print(f'Failed to remove the database {database_name}!')
            return False

    def get_cols(self, database_name: str, table_name: str) -> list:
        '''
        Get the column names of the table.
        Return the list of column names.
        '''
        self._cursor.execute(f'SHOW COLUMNS FROM {database_name}.{table_name}')
        cols = [col[0] for col in self._cursor.fetchall()]
        return cols

    def write_data(self, database_name: str, table_name: str, data: list) -> bool:
        '''
        Write the data to the table.
        Return the result of the write.
        '''
        try:
            cols = self.get_cols(database_name, table_name)
            print("current colunms:", cols)
            if "id" in cols:
                cols.remove("id")  # 控制 属性的个数
            # if "create_time" in cols:
            #     cols.remove("create_time")  # 控制 属性的个数
            # if "enable" in cols:
            #     cols.remove("enable")  # 控制 属性的个数

            sql = f'INSERT INTO {database_name}.{table_name} ({", ".join(cols)}) VALUES ({", ".join(["%s"] * len(cols))})'
            self._cursor.executemany(sql, data)

            self._db.commit()

            print(f'Data written to {database_name}.{table_name}!')
            return True
        except:
            print(f'Failed to write data to {database_name}.{table_name}!')
            return False

    def read_data(self, database_name: str, table_name: str) -> list:
        '''
        Read the data from the table.
        Return the data as a list of tuples.
        '''
        try:
            self._cursor.execute(f'SELECT * FROM {database_name}.{table_name}')
            data = list(self._cursor.fetchall())
            return [i for i in data]
            return [list(i) for i in data]
        except:
            print(f'Failed to read data from {database_name}.{table_name}!')
            return []

    def update_data(self, database_name: str, table_name: str, data: list) -> bool:
        '''
        Update the data in the table.
        Return the result of the update.
        '''
        try:
            cols = self.get_cols(database_name, table_name)
            if "project_uid" in cols:
                cols.remove("project_uid")
            if "create_time" in cols:
                cols.remove("create_time")
            if "orchestration" in cols:
                cols.remove("orchestration")
            # if "enable" in cols:
            #     cols.remove("enable")
            print(cols)
            sql = f'UPDATE {database_name}.{table_name} SET {", ".join([f"{col} = %s" for col in cols[1:]])} WHERE {cols[0]} = %s'
            print(sql)
            sorted_data = []
            for i in data:
                sorted_data.append(i[1:] + [i[0]])
            print(sorted_data)
            self._cursor.executemany(sql, sorted_data)
            self._db.commit()
            print(f'Data updated in {database_name}.{table_name}!')
            return True
        except:
            print(f'Failed to update data in {database_name}.{table_name}!')
            return False

    def remove_data_by_ids(self, database_name: str, table_name: str, ids: list) -> bool:
        '''
        Remove the data from the table by ids.
        Return the result of the removal.
        '''
        try:
            sql = f'DELETE FROM {database_name}.{table_name} WHERE id = %s'
            self._cursor.executemany(sql, ids)
            self._db.commit()
            # obj.connect_with_ssh().commit()
            print(f'Data removed from {database_name}.{table_name}!')
            return True
        except:
            print(f'Failed to remove data from {database_name}.{table_name}!')
            print(f'SQL: {sql}')
            return False

    def new_read_data(self, database_name: str, table_name: str, list_query=[], other_query='') -> list:
        '''
        Read the data from the table.
        Return the data as a list of tuples.
        '''
        try:
            sql = f'SELECT * FROM {database_name}.{table_name}'
            if other_query:
                sql += f' {other_query}'
            if list_query:
                sql = sql.replace('*', ', '.join(list_query))
            # print(sql)
            self._cursor.execute(sql)
            data = list(self._cursor.fetchall())  # [(),()]
            return [list(i) for i in data]  # [[],[]]
        except:
            print(f'Failed to read data from {database_name}.{table_name}!')
            print(f'SQL: {sql}')

            return []

    def update_one_field(self, database_name: str, table_name: str, data: list) -> bool:
        '''
        Update the data in the table.
        Return the result of the update.
        '''
        try:
            cols = ["id", "point"]
            sql = f'UPDATE {database_name}.{table_name} SET {", ".join([f"{col} = %s" for col in cols[1:]])} WHERE {cols[0]} = %s'

            sorted_data = []
            for i in data:
                sorted_data.append(i[1:] + [i[0]])
            print(sorted_data, "1")
            self._cursor.executemany(sql, sorted_data)
            self._db.commit()
            print(f'Data updated in {database_name}.{table_name}!')
            return True
        except Exception as e:
            print(f'Failed to update data in {database_name}.{table_name}!')
            return False

    def check_data_unique(self, database_name: str, table_name: str, data: list) -> bool:
        try:
            data = data[0]
            # SELECT `id`,`tag_no`,`remarks` FROM project_f8d92e473100.interlock WHERE tag_no = "LIA-0101"
            self._cursor.execute(
                f'SELECT `tag_no` FROM {database_name}.{table_name} WHERE tag_no = "{data}"')
            data = list(self._cursor.fetchall())
            data_real = data[0][0]
            if data_real:
                print(f'Data already exist in {database_name}.{table_name}!')
                return True
        except Exception as e:
            print(e)
            print(f'Failed to update data in {database_name}.{table_name}!')
            return False

    def write_file_data(self, database_name: str, table_name: str, data: list) -> bool:
        '''
        Write the data to the table.
        Return the result of the write.
        '''
        try:
            # cols = self.get_cols(database_name, table_name)
            cols = ["origin_no", 'tag_no', 'interlock_type',
                    'remarks', 'category_id', "function_id"]
            sql = f'INSERT INTO {database_name}.{table_name} ({", ".join(cols)}) VALUES ({", ".join(["%s"] * len(cols))})'
            self._cursor.executemany(sql, data)
            self._db.commit()
            print(f'Data written to {database_name}.{table_name}!')
            return True
        except:
            print(f'Failed to write data to {database_name}.{table_name}!')
            return False

    def update_any_data(self, database_name: str, table_name: str, data: list, update_column: list) -> bool:
        '''
        Update the data in the table.
        Return the result of the update.
        '''
        try:
            cols = update_column
            print("current update column: ", cols)
            sql = f'UPDATE {database_name}.{table_name} SET {", ".join([f"{col} = %s" for col in cols[1:]])} WHERE {cols[0]} = %s'
            sorted_data = []
            for i in data:
                sorted_data.append(i[1:] + [i[0]])
            self._cursor.executemany(sql, sorted_data)
            self._db.commit()
            print(f'Data updated in {database_name}.{table_name}!')
            return True
        except Exception as e:
            print(f'Failed to update data in {database_name}.{table_name}!')
            return False

    def write_any_data(self, database_name: str, table_name: str, data: list) -> bool:
        '''
        Write the data to the table.
        Return the result of the write.
        '''
        try:
            cols = self.get_cols(database_name, table_name)
            if "create_time" in cols:
                cols.remove("create_time")  # 控制 属性的个数
            if "enable" in cols:
                cols.remove("enable")  # 控制 属性的个数
            # if "network" in cols:
            #     cols.remove("network")  # 控制 属性的个数
            if "sort_id" in cols:
                cols.remove("sort_id")  # 控制 属性的个数
            sql = f'INSERT INTO {database_name}.{table_name} ({", ".join(cols)}) VALUES ({", ".join(["%s"] * len(cols))})'
            self._cursor.executemany(sql, data)

            self._db.commit()

            print(f'Data written to {database_name}.{table_name}!')
            return True
        except:
            print(f'Failed to write data to {database_name}.{table_name}!')
            return False

    def remove_data_by_code(self, database_name: str, table_name: str, ids: list) -> bool:
        '''
        Remove the data from the table by ids.
        Return the result of the removal.
        '''
        try:
            sql = f'DELETE FROM {database_name}.{table_name} WHERE `code` = %s'
            self._cursor.executemany(sql, ids)
            self._db.commit()
            # obj.connect_with_ssh().commit()
            print(f'Data removed from {database_name}.{table_name}!')
            return True
        except:
            print(f'Failed to remove data from {database_name}.{table_name}!')
            print(f'SQL: {sql}')
            return False

    def get_length(self, database_name: str, table_name: str, sql_query: str = None):
        '''
        Remove the data from the table by ids.
        Return the result of the removal.
        '''
        try:
            sql_query = f"SELECT COUNT(*) FROM {database_name}.{table_name}"
            print(sql_query)
            # if sql_query:
            #     sql = f"SELECT COUNT(*) FROM {database_name}.{table_name} WHERE "
            #     res = sql + sql_query

            self._cursor.execute(sql_query)
            result = self._cursor.fetchone()

            return result[0]

        except Exception as e:
            print(e)
            print(f'Failed to get lenth from {database_name}.{table_name}!')
            # print(f'SQL: {res}')
            return False

    def update_loss_data(self, database_name: str, table_name: str, data: list) -> bool:
        '''
        Update the data in the table.
        Return the result of the update.
        '''
        try:
            cols = self.get_cols(database_name, table_name)
            if "enable" in cols:
                cols.remove("enable")
            if "code" in cols:
                cols.insert(0, "code")
            if "create_time" in cols:
                cols.remove("create_time")
            if "orchestration" in cols:
                cols.remove("orchestration")

            print(cols)
            sql = f'UPDATE {database_name}.{table_name} SET {", ".join([f"{col} = %s" for col in cols[1:]])} WHERE {cols[0]} = %s'
            print(sql)
            sorted_data = []
            for i in data:
                sorted_data.append(i[1:] + [i[0]])
            print(sorted_data)
            self._cursor.executemany(sql, sorted_data)
            self._db.commit()
            print(f'Data updated in {database_name}.{table_name}!')
            return True
        except:
            print(f'Failed to update data in {database_name}.{table_name}!')
            return False

    def insert_one_filed(self, database_name: str, table_name: str, code: str, filed: str):

        try:
            # UPDATE student SET age = 18 WHERE id = 1;
            # INSERT INTO student(id, age) VALUES(1, 18);
            #  sql = "UPDATE student SET age = %s WHERE id = %s"
            # UPDATE code_function_block SET orchestration = "aaaa"  WHERE `code` = 'FB6001'
            sql = f"UPDATE {database_name}.{table_name} SET orchestration = '{filed}'  WHERE code = '{code}'"
            self._cursor.execute(sql)
            self._db.commit()
            print(f'Data written to {database_name}.{table_name}!')
            return True
        except:
            print(f'Failed to write data to {database_name}.{table_name}!')
            return False

    def insert_specific_field(self, database_name: str, table_name: str, code: str, filed: str):

        try:
            # UPDATE student SET age = 18 WHERE id = 1;
            # INSERT INTO student(id, age) VALUES(1, 18);
            #  sql = "UPDATE student SET age = %s WHERE id = %s"
            # UPDATE code_function_block SET orchestration = "aaaa"  WHERE `code` = 'FB6001'
            sql = f"UPDATE {database_name}.{table_name} SET orchestration = '{filed}'  WHERE code = '{code}'"
            self._cursor.execute(sql)
            self._db.commit()
            print(f'Data written to {database_name}.{table_name}!')
            return True
        except:
            print(f'Failed to write data to {database_name}.{table_name}!')
            return False

    def remove_data_by_field(self, database_name: str, table_name: str, ids: list, tag) -> bool:
        '''
        Remove the data from the table by ids.
        Return the result of the removal.
        '''
        try:
            if tag == "id":
                sql = f'DELETE FROM {database_name}.{table_name} WHERE id = %s'
            else:
                sql = f'DELETE FROM {database_name}.{table_name} WHERE {tag} = %s'

            self._cursor.executemany(sql, ids)
            self._db.commit()
            # obj.connect_with_ssh().commit()
            print(f'Data removed from {database_name}.{table_name}!')
            return True
        except:
            print(f'Failed to remove data from {database_name}.{table_name}!')
            print(f'SQL: {sql}')
            return False


if __name__ == '__main__':
    db_connector = DBConnector()
    print(db_connector.run())
    db_name = 'common'
    database_name = 'project_01'
    # db_connector.create_common_database()
    # db_connector.create_project_database("project_01")
    import json
    d = {"name": "zs", "age": 18}
    # device = ["docker", "DB2016", json.dumps(d), "desc"]
    # for i in range(30):
    #     d = {"name": "zs", "age": i}
    #     device = [f"docker{i}", f"DB201{i}", json.dumps(d), f"desc{i}"]
    #     db_connector.write_data(database_name, "device", [device])
    # slecet * from table where id =1
    # slecet * from table where id =1
    datas = db_connector.new_read_data(database_name, "device")

    for data in datas:
        print(data)
        # print(data[3], type(json.loads(data[3])))
    # # new_data = [1, "docker", "DB9999", json.dumps(d), "desc"]
    # new_data = [1, "liangizlong"]
    # # db_connector.update_data(database_name, "device", [new_data])
    # db_connector.update_any_data(database_name, "device", [
    #                              new_data], ["id", "description"])
    # db_connector.update_loss_data()
    # db_connector.update_one_data()
    # cdoe  name module_type description enable  vars spares
    # s1 = [{"a": {"c": "fff"}}]
    # s2 = [{"a": "b", "c": "D"}]
    # code_data_blcok = ["SS3", "DB30135", "DEV", "desc1",
    #                    1, json.dumps(s1), json.dumps(s2)]
    # db_connector.write_data(
    #     database_name, "code_data_block", [code_data_blcok])
    # datas = db_connector.new_read_data(database_name, "code_data_block")
    # # print(datas[0])
    # for data in datas:
    #     print(data[4], type(data[4]))
    #     print(data[5], type(data[5]), type(json.loads(data[5])))
    # db_connector.update_any_data()
    # db_connector.update_data(database_name, "code_data_block", ["", "xx"])
    # db_connector.update_any_data(database_name, "code_data_block", [[
    #                              "SS1", "xx"]], ["code", "description"])

    # # db_connector.remove_data_by_code(database_name, "code_data_block", ["fff"]) success
    # db_connector.remove_data_by_field(
    #     database_name, "code_data_block", ["SS3"], "code")  # SS3
    print(db_connector.get_length(database_name, "code_data_block"))
    # todo

    # db_connector.write_data("common", "user", [["zs", "ls"]])
    # datas = db_connector.new_read_data(database_name, "device")
    # # slecet * from table where id =1
    # r_data = db_connector.remove_data_by_ids(
    #     database_name, "device", [31, 32])  # 主键id
    # print(r_data)

    # # select * from table  all field
    # for item in db_connector.read_data(db_name, table_name=table_name):
    #     print(item)
    # print()
    # list_query=[] #想要的字段 #other_query='' #其他条件
    # db_connector.new_read_data()

    # INSERT INTO 表名（字段名1,字段名2,...) VALUES (值 1,值 2,...);
    # update student set age=20,gender='female' where name='tom';
    # UPDATE students SET grade = '软件工程' WHERE name = '张三';
    # UPDATE 表名称 SET 列1 = 值1, 列2 = 值2, ...WHERE 条件;
